package com.telecom.sxint.app.api.controller.SystemController.App1SystemFileController;
import cn.hutool.core.util.StrUtil;
import cn.zjtele.pubinfo.boot.redis.config.RedisHelper;
import cn.zjtele.pubinfo.common.http.response.ResponseData;
import com.telecom.sxint.app.core.domain.entity.Systempojo.App1Monthlypojo.App1Assessment;
import com.telecom.sxint.app.core.domain.entity.Systempojo.App1YunwangWorkpojo.App1YunwangPublicSheet;
import com.telecom.sxint.app.core.domain.entity.Systempojo.App1YunwangWorkpojo.App1YunwangWorkEvaluationIndicators;
import com.telecom.sxint.app.core.domain.entity.Systempojo.App1YunwangWorkpojo.App1YunwangWorkEvaluationIndicatorsData;
import com.telecom.sxint.app.core.domain.entity.Systempojo.App1countyIndicatorspojo.App1CountyIndicators;
import com.telecom.sxint.app.core.domain.entity.Systempojo.App1countyIndicatorspojo.App1QuarterPublicSheet;
import com.telecom.sxint.app.core.service.Systemservice.App1Monthlyservice.IApp1AssessmentService;
import com.telecom.sxint.app.core.service.Systemservice.App1YunwangWorkservice.IApp1YunwangPublicSheetService;
import com.telecom.sxint.app.core.service.Systemservice.App1YunwangWorkservice.IApp1YunwangWorkEvaluationIndicatorsDataService;
import com.telecom.sxint.app.core.service.Systemservice.App1YunwangWorkservice.IApp1YunwangWorkEvaluationIndicatorsService;
import com.telecom.sxint.app.core.service.Systemservice.App1countyIndicatorsservice.IApp1CountyIndicatorsService;
import com.telecom.sxint.app.core.service.Systemservice.App1countyIndicatorsservice.IApp1QuarterPublicSheetService;
import io.swagger.v3.oas.annotations.Operation;
import io.swagger.v3.oas.annotations.tags.Tag;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.apache.poi.ss.usermodel.*;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import java.sql.Timestamp;
import org.springframework.web.bind.annotation.RestController;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

/**
 * <p>
 @@ -15,8 +37,136 @@ import org.springframework.web.bind.annotation.RestController;
  * @since 2024-08-23
 */
@RestController
@Tag(name = "导入导出控制器", description = "导入导出控制器")
@RequestMapping("/app1-system")
public class App1SystemFileUpload {
    @Autowired
    private IApp1YunwangWorkEvaluationIndicatorsService iApp1YunwangWorkEvaluationIndicatorsService;
    @Autowired
    private IApp1YunwangWorkEvaluationIndicatorsDataService iApp1YunwangWorkEvaluationIndicatorsDataService;
    @Autowired
    private IApp1AssessmentService iApp1AssessmentService;
    @Autowired
    private IApp1QuarterPublicSheetService iApp1QuarterPublicSheetService;
    @Autowired
    private IApp1CountyIndicatorsService iApp1CountyIndicatorsService;
    @Autowired
    private IApp1YunwangPublicSheetService iApp1YunwangPublicSheetService;
    @Autowired
    private RedisHelper redisHelper;
    private static String getCellValue(Cell cell) {
        if (cell != null) {
            switch (cell.getCellType()) {
                case STRING:
                    return cell.getStringCellValue();
                case NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        return new Timestamp(cell.getDateCellValue().getTime()).toString();
                    } else {
                        return String.valueOf(cell.getNumericCellValue());
                    }
                case BOOLEAN:
                    return String.valueOf(cell.getBooleanCellValue());
                case ERROR:
                    // 错误类型的单元格可以返回错误信息或者 null
                    return "Error: " + cell.getErrorCellValue();
                case BLANK:
                    // 空白单元格可以根据需要返回 null 或者其他占位符
                    return "";
                default:
                    // 对于其他未处理的单元格类型，返回字符串表示形式
                    return cell.toString();
            }
        }
        return "";
    }

    public static String getCellValue2(Cell cell, FormulaEvaluator evaluator) {
        if (cell != null) {
            switch (cell.getCellType()) {
                case STRING:
                    return cell.getStringCellValue();
                case NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        return new Timestamp(cell.getDateCellValue().getTime()).toString();
                    } else {
                        return String.valueOf(cell.getNumericCellValue());
                    }
                case BOOLEAN:
                    return String.valueOf(cell.getBooleanCellValue());
                case ERROR:
                    return "Error: " + cell.getErrorCellValue();
                case FORMULA:
                    // 如果是公式单元格，获取计算结果
                    if (evaluator != null) {
                        try {
                            switch (evaluator.evaluateFormulaCell(cell)) {
                                case NUMERIC:
                                    return String.valueOf(cell.getNumericCellValue());
                                case STRING:
                                    return cell.getStringCellValue();
                                case BOOLEAN:
                                    return String.valueOf(cell.getBooleanCellValue());
//                                case ERROR:
//                                    return "Error: " + cell.getErrorCellValue();
                                default:
                                    // 如果公式计算结果不是上述类型之一，则返回公式文本
                                    return "="+cell.getCellFormula();
                            }
                        } catch (Exception e) {
                            // 如果公式计算出错，则返回公式文本
                            return cell.getCellFormula();
                        }
                    } else {
                        // 如果没有提供 evaluator，则返回公式文本
                        return cell.getCellFormula();
                    }
                case BLANK:
                    return "";
                default:
                    return cell.toString();
            }
        }
        return "";
    }
    /**
     * 云网工作评价指标体系excel文档录入数据库
     * @param file
     * @return
     */
    @Operation(summary = "云网工作评价指标体系excel文档录入数据库", description = "返回测试信息表分页")
    @PostMapping("/App1YunwangWorkEvaluationIndicatorsFileupload")
    public ResponseData importindicatorsExcel(@RequestParam("file") MultipartFile file) {
        redisHelper.deletePattern("System_*");
        if (file.isEmpty()) {
            return ResponseData.failed("没有文件","4101");
        }
        //判断为xls或xlsx文件
        String fileName = file.getOriginalFilename();
        if (!fileName.endsWith(".xls") && !fileName.endsWith(".xlsx")) {
            return ResponseData.failed("文件格式错误1","4103");
        }
        try (InputStream inputStream = file.getInputStream();
             Workbook workbook = WorkbookFactory.create(inputStream)) {

            Sheet sheet = workbook.getSheetAt(0);
            if (sheet == null) {
                // 工作表为空，返回错误信息或空列表
                return ResponseData.failed("没有可读取的工作表","4100");
            }
            if(!sheet.getRow(1).getCell(0).getStringCellValue().equals("考核项目") ||
                    !sheet.getRow(1).getCell(1).getStringCellValue().equals("大指标")||
                    !sheet.getRow(1).getCell(2).getStringCellValue().equals("大指标分值") ||
                    !sheet.getRow(1).getCell(3).getStringCellValue().equals("考核数据的提供部门和取数的系统")||
                    !sheet.getRow(1).getCell(4).getStringCellValue().equals("考核周期")||
                    !sheet.getRow(1).getCell(5).getStringCellValue().equals("目标值")||
                    !sheet.getRow(1).getCell(6).getStringCellValue().equals("指标定义（解释）")||
                    !sheet.getRow(1).getCell(7).getStringCellValue().equals("扣加分方法")) {
                return ResponseData.failed("文件格式错误2","4103");
            }
            List<App1YunwangWorkEvaluationIndicators> app1= new ArrayList<>();
            for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
                if (i == 0||i==1||getCellValue(sheet.getRow(i).getCell(0)).equals("合计")||
                        getCellValue(sheet.getRow(i).getCell(0)).equals("排名")) continue; // Skip header row if present

                App1YunwangWorkEvaluationIndicators log = new App1YunwangWorkEvaluationIndicators();
                if(StrUtil.isBlank(getCellValue(sheet.getRow(i).getCell(0)))){
                    sheet.getRow(i).getCell(0).setCellValue(getCellValue(sheet.getRow(i-1).getCell(0)));
                }
                log.setEvaluationItem(getCellValue(sheet.getRow(i).getCell(0)));
                log.setMainIndicator(getCellValue(sheet.getRow(i).getCell(1)));
                log.setMainIndicatorScore(getCellValue(sheet.getRow(i).getCell(2)));
                log.setDataProvisionDepartment(getCellValue(sheet.getRow(i).getCell(3)));
                log.setEvaluationPeriod(getCellValue(sheet.getRow(i).getCell(4)));
                log.setTargetValue(getCellValue(sheet.getRow(i).getCell(5)));
                log.setIndicatorDefinition(getCellValue(sheet.getRow(i).getCell(6)));
                log.setDeductionAdditionMethod(getCellValue(sheet.getRow(i).getCell(7)));
                app1.add(log);
            }
            iApp1YunwangWorkEvaluationIndicatorsService.saveBatch(app1);
            return ResponseData.success(app1);

        } catch (IOException e) {
            return ResponseData.failed("导入失败","4102");
        }
    }

    /**
     * 季度excel导入数据库
     * @param file
     * @return
     */
    @Operation(summary = "季度excel导入数据库", description = "季度excel倒数数据库")
    @PostMapping("/App1QuarterPublicSheetFileupload")
    private ResponseData importExcel3(@RequestParam("file") MultipartFile file) {
        if (file.isEmpty()) {
            return ResponseData.failed("没有文件","4101");
        }
        //判断为xls或xlsx文件
        String fileName = file.getOriginalFilename();
        if (!fileName.endsWith(".xls") && !fileName.endsWith(".xlsx")) {
            return ResponseData.failed("文件格式错误1","4103");
        }
        try (InputStream inputStream = file.getInputStream();
             Workbook workbook = WorkbookFactory.create(inputStream)) {

            Sheet sheet1 = workbook.getSheetAt(0);
            if (sheet1 == null) {
                // 工作表为空，返回错误信息或空列表
                return ResponseData.failed("没有可读取的工作表", "4100");
            }
            List<App1QuarterPublicSheet> app1QuarterPublicSheets = new ArrayList<>();

            int numberOfSheets = workbook.getNumberOfSheets();
            Sheet[] sheets = new Sheet[numberOfSheets];
            // 将每个Sheet存储到数组中
            for (int i = 0; i < numberOfSheets; i++) {
                sheets[i] = workbook.getSheetAt(i);
            }
            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            // 遍历数组，访问每个Sheet
            for (Sheet sheet : sheets) {

                String sheetName = sheet.getSheetName();
                List<App1CountyIndicators> app1CountyIndicators= new ArrayList<>();
                if (Objects.equals(sheetName, "县分指标")) {
                    Row firstRow1 = sheet.getRow(1);
                    if (firstRow1 != null) {
                        for (Row row : sheet) {
                            if(row.getRowNum()==0||row.getRowNum()==1||getCellValue(row.getCell(1)).equals("合计")) continue;
                            App1CountyIndicators log = new App1CountyIndicators();
                            log.setIndicatorName(getCellValue(row.getCell(1)));
                            log.setScore(getCellValue(row.getCell(2)));
                            log.setAssessmentMethod(getCellValue(row.getCell(3)));
                            log.setProvider(getCellValue(row.getCell(4)));
                            log.setIsDelete(0);
                            app1CountyIndicators.add(log);
                        }
                    }
                    iApp1CountyIndicatorsService.saveBatch(app1CountyIndicators);
                }
                Row firstRow = sheet.getRow(0);

// 将获取合并区域的调用移到循环外部
                List<CellRangeAddress> mergedRegions = new ArrayList<>();
                for (CellRangeAddress mergedRegion : sheet.getMergedRegions()) {
                    mergedRegions.add(mergedRegion);
                }

                for (Row row : sheet) {
                    App1QuarterPublicSheet log = new App1QuarterPublicSheet();
                    for (int i = 0; i < 15; i++) {
                        Cell cell = row.getCell(i, Row.MissingCellPolicy.RETURN_NULL_AND_BLANK);
                        if (cell == null || cell.getCellType() == CellType.BLANK) {
                            for (CellRangeAddress mergedRegion : mergedRegions) { // 使用预先获取的合并区域列表
                                if (mergedRegion.isInRange(row.getRowNum(), i)) {
                                    // 如果当前单元格是合并的起始单元格，获取其值
                                    Row startRow = sheet.getRow(mergedRegion.getFirstRow());
                                    Cell startCell = startRow.getCell(mergedRegion.getFirstColumn());
                                    String cellValue = getCellValue(startCell);
                                    row.getCell(i).setCellValue(cellValue);
                                    break;
                                }
                            }
                        }
                    }
                    String isname = getCellValue(row.getCell(0));
                    String jiakoufen = getCellValue(row.getCell(6));
                    if (Objects.equals(isname, "区县公司") || Objects.equals(isname, "区县") || Objects.equals(isname, "2024年线路迁改赔补考核")|| Objects.equals(isname, "政企交付满意度-二季度")|| Objects.equals(isname, "政企交付满意度")|| Objects.equals(isname, "县分") || Objects.equals(isname, "区域") || Objects.equals(jiakoufen, "加扣分") || Objects.equals(isname, "序号")) {
                        log.setIsField("true");
                    } else {
                        log.setIsField("false");
                    }

                    log.setSheetName(sheetName);
                    log.setGeneralFieldA(getCellValue2(row.getCell(0),evaluator));
                    log.setGeneralFieldB(getCellValue2(row.getCell(1),evaluator));
                    log.setGeneralFieldC(getCellValue2(row.getCell(2),evaluator));
                    log.setGeneralFieldD(getCellValue2(row.getCell(3),evaluator));
                    log.setGeneralFieldE(getCellValue2(row.getCell(4),evaluator));
                    log.setGeneralFieldF(getCellValue2(row.getCell(5),evaluator));
                    log.setGeneralFieldG(getCellValue2(row.getCell(6),evaluator));
                    log.setGeneralFieldH(getCellValue2(row.getCell(7),evaluator));
                    log.setGeneralFieldI(getCellValue2(row.getCell(8),evaluator));
                    log.setGeneralFieldJ(getCellValue2(row.getCell(9),evaluator));
                    log.setGeneralFieldK(getCellValue2(row.getCell(10),evaluator));
                    log.setGeneralFieldL(getCellValue2(row.getCell(11),evaluator));
                    log.setGeneralFieldM(getCellValue2(row.getCell(12),evaluator));
                    log.setGeneralFieldN(getCellValue2(row.getCell(13),evaluator));
                    log.setGeneralFieldO(getCellValue2(row.getCell(14),evaluator));

                    app1QuarterPublicSheets.add(log);
                }



            }
            iApp1QuarterPublicSheetService.saveBatch(app1QuarterPublicSheets);
            return ResponseData.success();

        } catch (IOException e) {
            return ResponseData.failed("导入失败","4102");
        }
    }

    /**
     * 云网工作评价指标体系excel文档录入数据库
     * @param file
     * @return
     */
    @Operation(summary = "区县数据excel文档录入数据库", description = "返回测试信息表分页")
    @PostMapping("/App1YunwangWorkEvaluationIndicatorsDataFileupload")
    public ResponseData importDataExcel(@RequestParam("file") MultipartFile file) {
        if (file.isEmpty()) {
            return ResponseData.failed("没有文件","4101");
        }
        //判断为xls或xlsx文件
        String fileName = file.getOriginalFilename();
        if (!fileName.endsWith(".xls") && !fileName.endsWith(".xlsx")) {
            return ResponseData.failed("文件格式错误1","4103");
        }
        try (InputStream inputStream = file.getInputStream();
             Workbook workbook = WorkbookFactory.create(inputStream)) {

            Sheet sheet = workbook.getSheetAt(0);
            if (sheet == null) {
                // 工作表为空，返回错误信息或空列表
                return ResponseData.failed("没有可读取的工作表","4100");
            }
            if(!sheet.getRow(1).getCell(8).getStringCellValue().equals("越城") ||
                    !sheet.getRow(1).getCell(9).getStringCellValue().equals("柯桥")||
                    !sheet.getRow(1).getCell(10).getStringCellValue().equals("上虞") ||
                    !sheet.getRow(1).getCell(11).getStringCellValue().equals("嵊州")||
                    !sheet.getRow(1).getCell(12).getStringCellValue().equals("新昌")||
                    !sheet.getRow(1).getCell(13).getStringCellValue().equals("诸暨")) {
                return ResponseData.failed("文件格式错误2","4103");
            }
            List<App1YunwangWorkEvaluationIndicatorsData> app1= new ArrayList<>();
            for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
                if (i == 0||i==1||getCellValue(sheet.getRow(i).getCell(0)).equals("合计")||
                        getCellValue(sheet.getRow(i).getCell(0)).equals("排名")) continue; // Skip header row if present

                App1YunwangWorkEvaluationIndicatorsData log = new App1YunwangWorkEvaluationIndicatorsData();
                if(StrUtil.isBlank(getCellValue(sheet.getRow(i).getCell(0)))){
                    sheet.getRow(i).getCell(0).setCellValue(getCellValue(sheet.getRow(i-1).getCell(0)));
                }
                log.setEvaluationItem(getCellValue(sheet.getRow(i).getCell(0)));
                log.setMainIndicator(getCellValue(sheet.getRow(i).getCell(1)));
                log.setDataProvisionDepartment(getCellValue(sheet.getRow(i).getCell(3)));
                log.setYuecheng(getCellValue(sheet.getRow(i).getCell(8)));
                log.setKeqiao(getCellValue(sheet.getRow(i).getCell(9)));
                log.setShangyu(getCellValue(sheet.getRow(i).getCell(10)));
                log.setShengzhou(getCellValue(sheet.getRow(i).getCell(11)));
                log.setXinchang(getCellValue(sheet.getRow(i).getCell(12)));
                log.setZhuji(getCellValue(sheet.getRow(i).getCell(13)));
                app1.add(log);
            }
            iApp1YunwangWorkEvaluationIndicatorsDataService.saveBatch(app1);
            return ResponseData.success(app1);

        } catch (IOException e) {
            return ResponseData.failed("导入失败","4102");
        }
    }

    /**
     * 云网工作评价指标体系+数据excel录入数据库
     * @param file
     * @return
     */
    @Operation(summary = "云网工作评价指标体系+数据excel文档录入数据库", description = "返回测试信息表分页")
    @PostMapping("/App1YunwangWorkEvaluationIndicatorsAndDataFileupload")
    public ResponseData importYunwangExcel(@RequestParam("file") MultipartFile file) {
        if (file.isEmpty()) {
            return ResponseData.failed("没有文件","4101");
        }
        //判断为xls或xlsx文件
        String fileName = file.getOriginalFilename();
        if (!fileName.endsWith(".xls") && !fileName.endsWith(".xlsx")) {
            return ResponseData.failed("文件格式错误1","4103");
        }
        try (InputStream inputStream = file.getInputStream();
             Workbook workbook = WorkbookFactory.create(inputStream)) {

            Sheet sheet = workbook.getSheetAt(0);
            if (sheet == null) {
                // 工作表为空，返回错误信息或空列表
                return ResponseData.failed("没有可读取的工作表","4100");
            }
            List<App1YunwangWorkEvaluationIndicators> indicators= new ArrayList<>();
            List<App1YunwangWorkEvaluationIndicatorsData> indicatorsData= new ArrayList<>();
            for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
                if (i == 0||i==1||getCellValue(sheet.getRow(i).getCell(0)).equals("合计")||
                        getCellValue(sheet.getRow(i).getCell(0)).equals("排名")) continue; // Skip header row if present

                App1YunwangWorkEvaluationIndicators log1 = new App1YunwangWorkEvaluationIndicators();
                App1YunwangWorkEvaluationIndicatorsData log2 = new App1YunwangWorkEvaluationIndicatorsData();
                if(StrUtil.isBlank(getCellValue(sheet.getRow(i).getCell(0)))){
                    sheet.getRow(i).getCell(0).setCellValue(getCellValue(sheet.getRow(i-1).getCell(0)));
                }
                log1.setEvaluationItem(getCellValue(sheet.getRow(i).getCell(0)));
                log1.setMainIndicator(getCellValue(sheet.getRow(i).getCell(1)));
                log1.setMainIndicatorScore(getCellValue(sheet.getRow(i).getCell(2)));
                log1.setDataProvisionDepartment(getCellValue(sheet.getRow(i).getCell(3)));
                log1.setEvaluationPeriod(getCellValue(sheet.getRow(i).getCell(4)));
                log1.setTargetValue(getCellValue(sheet.getRow(i).getCell(5)));
                log1.setIndicatorDefinition(getCellValue(sheet.getRow(i).getCell(6)));
                log1.setDeductionAdditionMethod(getCellValue(sheet.getRow(i).getCell(7)));
                log2.setEvaluationItem(getCellValue(sheet.getRow(i).getCell(0)));
                log2.setMainIndicator(getCellValue(sheet.getRow(i).getCell(1)));
                log2.setDataProvisionDepartment(getCellValue(sheet.getRow(i).getCell(3)));
                log2.setYuecheng(getCellValue(sheet.getRow(i).getCell(8)));
                log2.setKeqiao(getCellValue(sheet.getRow(i).getCell(9)));
                log2.setShangyu(getCellValue(sheet.getRow(i).getCell(10)));
                log2.setShengzhou(getCellValue(sheet.getRow(i).getCell(11)));
                log2.setXinchang(getCellValue(sheet.getRow(i).getCell(12)));
                log2.setZhuji(getCellValue(sheet.getRow(i).getCell(13)));
                indicators.add(log1);
                indicatorsData.add(log2);
            }
            iApp1YunwangWorkEvaluationIndicatorsService.saveBatch(indicators);
            iApp1YunwangWorkEvaluationIndicatorsDataService.saveBatch(indicatorsData);
            return ResponseData.success(indicators+"------"+indicatorsData);

        } catch (IOException e) {
            return ResponseData.failed("导入失败","4102");
        }
    }



    /**
     * 各部门月度考核excel导入数据库
     * @param file
     * @return
     */
    @Operation(summary = "各部门月度考核excel导入数据库", description = "各部门月度考核excel倒数数据库")
    @PostMapping("/App1AssessmentFileupload")
    public ResponseData importMonthExcel(@RequestParam("file") MultipartFile file) {
        if (file.isEmpty()) {
            return ResponseData.failed("没有文件","4101");
        }
        //判断为xls或xlsx文件
        String fileName = file.getOriginalFilename();
        if (!fileName.endsWith(".xls") && !fileName.endsWith(".xlsx")) {
            return ResponseData.failed("文件格式错误1","4103");
        }
        try (InputStream inputStream = file.getInputStream();
             Workbook workbook = WorkbookFactory.create(inputStream)) {

            Sheet sheet1 = workbook.getSheetAt(0);
            if (sheet1 == null) {
                // 工作表为空，返回错误信息或空列表
                return ResponseData.failed("没有可读取的工作表","4100");
            }
            List<App1Assessment> assessment= new ArrayList<>();

            int numberOfSheets = workbook.getNumberOfSheets();
            Sheet[] sheets = new Sheet[numberOfSheets];
            // 将每个Sheet存储到数组中
            for (int i = 0; i < numberOfSheets; i++) {
                sheets[i] = workbook.getSheetAt(i);
            }
            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            // 遍历数组，访问每个Sheet
            for (Sheet sheet : sheets) {
                String sheetName=sheet.getSheetName();
                if (Objects.equals(sheetName, "汇总")||Objects.equals(sheetName, "政企中心")||Objects.equals(sheetName, "公共KPI指标")){
                    continue;
                }
                Row firstRow = sheet.getRow(1);
                //if (firstRow != null) {
                // 将获取合并区域的调用移到循环外部
                List<CellRangeAddress> mergedRegions = new ArrayList<>();
                for (CellRangeAddress mergedRegion : sheet.getMergedRegions()) {
                    mergedRegions.add(mergedRegion);
                }
                for (Row row : sheet) {
                    App1Assessment log = new App1Assessment();
                    for (int i = 0; i < 10; i++) {
                        Cell cell = row.getCell(i, Row.MissingCellPolicy.RETURN_NULL_AND_BLANK);
                        if (cell == null || cell.getCellType() == CellType.BLANK) {
                            for (CellRangeAddress mergedRegion : mergedRegions) { // 使用预先获取的合并区域列表
                                if (mergedRegion.isInRange(row.getRowNum(), i)) {
//                                           // 如果当前单元格是合并的起始单元格，获取其值
                                    Row startRow = sheet.getRow(mergedRegion.getFirstRow());
                                    Cell startCell = startRow.getCell(mergedRegion.getFirstColumn());


                                    String cellValue = getCellValue(startCell);
                                    row.getCell(i).setCellValue(cellValue);
                                    break;
                                }
                            }
                        }
                    }
                    if (row.getRowNum() == 0||row.getRowNum() == 1) continue;

                    log.setCategory(getCellValue(row.getCell(0)));
                    log.setAssessmentItem(getCellValue(row.getCell(1)));
                    log.setWeight(getCellValue(row.getCell(2)));
                    log.setTarget(getCellValue(row.getCell(3)));
                    log.setSelfScoringMethod(getCellValue(row.getCell(4)));
                    log.setNotes(getCellValue(row.getCell(5)));
                    log.setTargetValue(getCellValue2(row.getCell(6),evaluator));
                    log.setCompletionValue(getCellValue2(row.getCell(7),evaluator));
                    log.setCompletionRateRank(getCellValue2(row.getCell(8),evaluator));
                    log.setScore(getCellValue2(row.getCell(9),evaluator));
                    log.setAssessedDepartment(sheet.getSheetName());
                    assessment.add(log);
                }

            }
            iApp1AssessmentService.saveBatch(assessment);



            return ResponseData.success();

        } catch (IOException e) {
            return ResponseData.failed("导入失败","4102");
        }
    }

    @Operation(summary = "云网其他sheet导入数据库", description = "云网其他sheet导入数据库")
    @PostMapping("/YunwangSheetExcelFileupload")
    public ResponseData importYunwangSheetExcel(@RequestParam("file") MultipartFile file) {
        if (file.isEmpty()) {
            return ResponseData.failed("没有文件","4101");
        }
        //判断为xls或xlsx文件
        String fileName = file.getOriginalFilename();
        if (!fileName.endsWith(".xls") && !fileName.endsWith(".xlsx")) {
            return ResponseData.failed("文件格式错误1","4103");
        }
        try (InputStream inputStream = file.getInputStream();
             Workbook workbook = WorkbookFactory.create(inputStream)) {

            Sheet sheet1 = workbook.getSheetAt(0);
            if (sheet1 == null) {
                // 工作表为空，返回错误信息或空列表
                return ResponseData.failed("没有可读取的工作表","4100");
            }
            List<App1YunwangPublicSheet> app1YunwangPublicSheets= new ArrayList<>();

            int numberOfSheets = workbook.getNumberOfSheets();
            Sheet[] sheets = new Sheet[numberOfSheets];
            // 将每个Sheet存储到数组中
            for (int i = 0; i < numberOfSheets; i++) {
                sheets[i] = workbook.getSheetAt(i);
            }
            // 遍历数组，访问每个Sheet

            for (Sheet sheet : sheets) {
                String sheetName=sheet.getSheetName();
                if (Objects.equals(sheetName, "网运")){
                    continue;
                }
                Row firstRow = sheet.getRow(1);
                if (firstRow != null) {
                    for (Row row : sheet) {
                        int lastCellNum = row.getLastCellNum();
                        App1YunwangPublicSheet log = new App1YunwangPublicSheet();
                        if(getMergeCellValue(row, 0, sheet).equals("单位")||
                                getMergeCellValue(row, 0, sheet).equals("大类")||
                                getMergeCellValue(row, 0, sheet).equals("客调分局")||
                                getMergeCellValue(row, 0, sheet).equals("单位/指标")||
                                getMergeCellValue(row, 0, sheet).equals("区县")) {
                            log.setIsField("true");
                        }else {
                            log.setIsField("false");
                        }
                        log.setSheetName(sheetName);
                        log.setGeneralFieldA(getMergeCellValue(row, 0, sheet));
                        log.setGeneralFieldB(getMergeCellValue(row, 1, sheet));
                        log.setGeneralFieldC(getMergeCellValue(row, 2, sheet));
                        log.setGeneralFieldD(getMergeCellValue(row, 3, sheet));
                        log.setGeneralFieldE(getMergeCellValue(row, 4, sheet));
                        log.setGeneralFieldF(getMergeCellValue(row, 5, sheet));
                        log.setGeneralFieldG(getMergeCellValue(row, 6, sheet));
                        log.setGeneralFieldH(getMergeCellValue(row, 7, sheet));
                        log.setGeneralFieldI(getMergeCellValue(row, 8, sheet));
                        log.setGeneralFieldJ(getMergeCellValue(row, 9, sheet));
                        log.setGeneralFieldK(getMergeCellValue(row, 10, sheet));
                        log.setGeneralFieldL(getMergeCellValue(row, 11, sheet));
                        log.setGeneralFieldM(getMergeCellValue(row, 12, sheet));
                        log.setGeneralFieldN(getMergeCellValue(row, 13, sheet));
                        log.setGeneralFieldO(getMergeCellValue(row, 14, sheet));
                        if(sheetName.equals("光衰数据")){
                            System.out.println(getMergeCellValue(row, 5, sheet));
                            System.out.println("+++");
                            System.out.println(getMergeCellValue(row, 5, sheet));
                            System.out.println("+++");
                            System.out.println(getMergeCellValue(row, 5, sheet));
                            System.out.println("--------------------------------------");
                        }
                        app1YunwangPublicSheets.add(log);
                    }
                }
            }
            iApp1YunwangPublicSheetService.saveBatch(app1YunwangPublicSheets);
            return ResponseData.success();

        } catch (IOException e) {
            return ResponseData.failed("导入失败","4102");
        }
    }

    /**
     * 云网新增sheet导入数据库
     * @param file
     * @return
     */
    @Operation(summary = "云网新增sheet导入数据库", description = "云网新增sheet导入数据库")
    @PostMapping("/addYunwangSheet")
    public ResponseData addYunwangSheet(@RequestParam("file") MultipartFile file, @RequestParam List<String> headers) {
        if (file.isEmpty()) {
            return ResponseData.failed("没有文件","4101");
        }
        //判断为xls或xlsx文件
        String fileName = file.getOriginalFilename();
        if (!fileName.endsWith(".xls") && !fileName.endsWith(".xlsx")) {
            return ResponseData.failed("文件格式错误1","4103");
        }
        try (InputStream inputStream = file.getInputStream();
             Workbook workbook = WorkbookFactory.create(inputStream)) {

            Sheet sheet1 = workbook.getSheetAt(0);
            if (sheet1 == null) {
                // 工作表为空，返回错误信息或空列表
                return ResponseData.failed("没有可读取的工作表","4100");
            }
            List<App1YunwangPublicSheet> app1YunwangPublicSheets= new ArrayList<>();

            int numberOfSheets = workbook.getNumberOfSheets();
            Sheet[] sheets = new Sheet[numberOfSheets];
            // 将每个Sheet存储到数组中
            for (int i = 0; i < numberOfSheets; i++) {
                sheets[i] = workbook.getSheetAt(i);
            }
            // 遍历数组，访问每个Sheet
            System.out.println(headers);
            for (Sheet sheet : sheets) {
                String sheetName=sheet.getSheetName();
                Row firstRow = sheet.getRow(1);
                if (firstRow != null) {
                    for (Row row : sheet) {
                        System.out.println(getMergeCellValue(row, 0, sheet));
                        App1YunwangPublicSheet log = new App1YunwangPublicSheet();
                        if(getMergeCellValue(row, 0, sheet).equals(headers.get(0))){
                            log.setIsField("true");
                        }else {
                            log.setIsField("false");
                        }
                        log.setSheetName(sheetName);
                        log.setGeneralFieldA(getMergeCellValue(row, 0, sheet));
                        log.setGeneralFieldB(getMergeCellValue(row, 1, sheet));
                        log.setGeneralFieldC(getMergeCellValue(row, 2, sheet));
                        log.setGeneralFieldD(getMergeCellValue(row, 3, sheet));
                        log.setGeneralFieldE(getMergeCellValue(row, 4, sheet));
                        log.setGeneralFieldF(getMergeCellValue(row, 5, sheet));
                        log.setGeneralFieldG(getMergeCellValue(row, 6, sheet));
                        log.setGeneralFieldH(getMergeCellValue(row, 7, sheet));
                        log.setGeneralFieldI(getMergeCellValue(row, 8, sheet));
                        log.setGeneralFieldJ(getMergeCellValue(row, 9, sheet));
                        log.setGeneralFieldK(getMergeCellValue(row, 10, sheet));
                        log.setGeneralFieldL(getMergeCellValue(row, 11, sheet));
                        log.setGeneralFieldM(getMergeCellValue(row, 12, sheet));
                        log.setGeneralFieldN(getMergeCellValue(row, 13, sheet));
                        log.setGeneralFieldO(getMergeCellValue(row, 14, sheet));
                        app1YunwangPublicSheets.add(log);
                    }
                }
            }
            iApp1YunwangPublicSheetService.saveBatch(app1YunwangPublicSheets);
            return ResponseData.success();

        } catch (IOException e) {
            return ResponseData.failed("导入失败","4102");
        }
    }


    /**
     * 季度新增sheet导入数据库
     * @param file
     * @return
     */
    @Operation(summary = "季度新增sheet导入数据库", description = "季度新增sheet导入数据库")
    @PostMapping("/addQuarterSheet")
    public ResponseData addQuarterSheet(@RequestParam("file") MultipartFile file, @RequestParam List<String> headers) {
        if (file.isEmpty()) {
            return ResponseData.failed("没有文件","4101");
        }
        //判断为xls或xlsx文件
        String fileName = file.getOriginalFilename();
        if (!fileName.endsWith(".xls") && !fileName.endsWith(".xlsx")) {
            return ResponseData.failed("文件格式错误1","4103");
        }
        try (InputStream inputStream = file.getInputStream();
             Workbook workbook = WorkbookFactory.create(inputStream)) {

            Sheet sheet1 = workbook.getSheetAt(0);
            if (sheet1 == null) {
                // 工作表为空，返回错误信息或空列表
                return ResponseData.failed("没有可读取的工作表","4100");
            }
            List<App1QuarterPublicSheet> app1QuarterPublicSheets= new ArrayList<>();

            int numberOfSheets = workbook.getNumberOfSheets();
            Sheet[] sheets = new Sheet[numberOfSheets];
            // 将每个Sheet存储到数组中
            for (int i = 0; i < numberOfSheets; i++) {
                sheets[i] = workbook.getSheetAt(i);
            }
            // 遍历数组，访问每个Sheet
            System.out.println(headers);
            for (Sheet sheet : sheets) {
                String sheetName=sheet.getSheetName();
                Row firstRow = sheet.getRow(1);
                if (firstRow != null) {
                    for (Row row : sheet) {
                        System.out.println(getMergeCellValue(row, 0, sheet));
                        App1QuarterPublicSheet log = new App1QuarterPublicSheet();
                        if(getMergeCellValue(row, 0, sheet).equals(headers.get(0))){
                            log.setIsField("true");
                        }else {
                            log.setIsField("false");
                        }
                        log.setSheetName(sheetName);
                        log.setGeneralFieldA(getMergeCellValue(row, 0, sheet));
                        log.setGeneralFieldB(getMergeCellValue(row, 1, sheet));
                        log.setGeneralFieldC(getMergeCellValue(row, 2, sheet));
                        log.setGeneralFieldD(getMergeCellValue(row, 3, sheet));
                        log.setGeneralFieldE(getMergeCellValue(row, 4, sheet));
                        log.setGeneralFieldF(getMergeCellValue(row, 5, sheet));
                        log.setGeneralFieldG(getMergeCellValue(row, 6, sheet));
                        log.setGeneralFieldH(getMergeCellValue(row, 7, sheet));
                        log.setGeneralFieldI(getMergeCellValue(row, 8, sheet));
                        log.setGeneralFieldJ(getMergeCellValue(row, 9, sheet));
                        log.setGeneralFieldK(getMergeCellValue(row, 10, sheet));
                        log.setGeneralFieldL(getMergeCellValue(row, 11, sheet));
                        log.setGeneralFieldM(getMergeCellValue(row, 12, sheet));
                        log.setGeneralFieldN(getMergeCellValue(row, 13, sheet));
                        log.setGeneralFieldO(getMergeCellValue(row, 14, sheet));
                        app1QuarterPublicSheets.add(log);
                    }
                }
            }
            iApp1QuarterPublicSheetService.saveBatch(app1QuarterPublicSheets);
            return ResponseData.success();

        } catch (IOException e) {
            return ResponseData.failed("导入失败","4102");
        }
    }
    /**
     * 获取合并单元格的值
     *
     * @param row    行
     * @param colNum 列号
     * @param sheet  工作表
     * @return 合并单元格的值
     */
    private String getMergeCellValue(Row row, int colNum, Sheet sheet) {
        Cell cell = row.getCell(colNum, Row.MissingCellPolicy.RETURN_NULL_AND_BLANK);
        if (cell == null || cell.getCellType() == CellType.BLANK) {
            for (CellRangeAddress mergedRegion : sheet.getMergedRegions()) {
                if (mergedRegion.isInRange(row.getRowNum(), colNum)) {
                    Row startRow = sheet.getRow(mergedRegion.getFirstRow());
                    Cell startCell = startRow.getCell(mergedRegion.getFirstColumn());
                    if (startCell != null) {
                        return getCellValue(startCell);
                    }
                }
            }
            return ""; // 如果没有找到合并单元格或单元格为空，返回空字符串
        }
        return getCellValue(cell);
    }
}



